USE [NMPA]
drop table [InstrumentSale]
CREATE TABLE InstrumentSale(
	[ID] [bigint] NOT NULL,
	[CreateTime] [datetime] NULL,
	[UpdateTime] [datetime] NULL,
	[SALE_NO] [nvarchar](80) NULL,
	[SALE_DATE] [datetime] NULL,
	[INSTRUMENT_CODE] [nvarchar](40) NULL,
	[INSTRUMENT_NAME] [nvarchar](200) NULL,
	[REGIST_NO] [nvarchar](80) NULL,
	[BATCH_NO] [nvarchar](50) NULL,
	[VALIDITY] [datetime] NULL,
	[PRODUCER] [nvarchar](200) NULL,
	[SPEC] [nvarchar](100) NULL,
	[UNIT] [nvarchar](60) NULL,
	[QTY] [decimal](23, 10) NULL,
	[PURCHASER_NAME] [nvarchar](200) NULL,
	[PURCHASER_CODE] [nvarchar](40) NULL,
	[DESTROY_NO] [nvarchar](100) NULL,
	[ISDELETE] [bit] NULL
) ON [PRIMARY]

drop table InstrumentStock

CREATE TABLE InstrumentStock(
	[ID] [bigint] NOT NULL,
	[CreateTime] [datetime] NULL,
	[UpdateTime] [datetime] NULL,
	[PURCHASE_DATE] [datetime] NULL,
	[BUY_NO] [nvarchar](80) NULL,
	[INSTRUMENT_CODE] [nvarchar](50) NULL,
	[INSTRUMENT_NAME] [nvarchar](200) NULL,
	[REGIST_NO] [nvarchar](80) NULL,
	[BATCH_NO] [nvarchar](50) NULL,
	[VALIDITY] [datetime] NULL,
	[PRODUCER] [nvarchar](200) NULL,
	[SPEC] [nvarchar](100) NULL,
	[UNIT] [nvarchar](60) NULL,
	[QTY] [decimal](23, 10) NULL,
	[PROVIDER_NAME] [nvarchar](200) NULL,
	[PROVIDER_CODE] [nvarchar](40) NULL,
	[DESTROY_NO] [nvarchar](50) NULL,
	[ISDELETE] [bit] NULL
) ON [PRIMARY]

drop table InstrumentStore

CREATE TABLE InstrumentStore(
	[ID] [bigint] NOT NULL,
	[CreateTime] [datetime] NULL,
	[UpdateTime] [datetime] NULL,
	[INSTRUMENT_CODE] [nvarchar](40) NULL,
	[INSTRUMENT_NAME] [nvarchar](200) NULL,
	[REGIST_NO] [nvarchar](80) NULL,
	[BATCH_NO] [nvarchar](50) NULL,
	[VALIDITY] [datetime] NULL,
	[PRODUCER] [nvarchar](200) NULL,
	[SPEC] [nvarchar](100) NULL,
	[UNIT] [nvarchar](60) NULL,
	[QTY] [decimal](23, 10) NULL,
	[DESTROY_NO] [nvarchar](50) NULL,
	[ISDELETE] [bit] NULL
) ON [PRIMARY]

drop table [SALE]
CREATE TABLE [dbo].[SALE](
	[ID] [bigint] NOT NULL,
	[CreateTime] [datetime] NULL,
	[UpdateTime] [datetime] NULL,
	[SALE_NO] [nvarchar](80) NULL,
	[SALE_DATE] [datetime] NULL,
	[PHAM_CODE] [nvarchar](40) NULL,
	[PHAM_NAME] [nvarchar](200) NULL,
	[PASS_NO] [nvarchar](80) NULL,
	[BATCH_NO] [nvarchar](50) NULL,
	[VALIDITY] [datetime] NULL,
	[PRODUCER] [nvarchar](200) NULL,
	[SPEC] [nvarchar](100) NULL,
	[UNIT] [nvarchar](60) NULL,
	[QTY] [decimal](23, 10) NULL,
	[PURCHASER_NAME] [nvarchar](200) NULL,
	[PURCHASER_CODE] [nvarchar](40) NULL,
	[PROPERTY] [nvarchar](100) NULL,
	[ISDELETE] [bit] NULL
) ON [PRIMARY]

drop table [STOCK]
CREATE TABLE [dbo].[STOCK](
	[ID] [bigint] NOT NULL,
	[CreateTime] [datetime] NULL,
	[UpdateTime] [datetime] NULL,
	[PURCHASE_DATE] [datetime] NULL,
	[BUY_NO] [nvarchar](80) NULL,
	[PHAM_CODE] [nvarchar](50) NULL,
	[PHAM_NAME] [nvarchar](200) NULL,
	[PASS_NO] [nvarchar](80) NULL,
	[BATCH_NO] [nvarchar](50) NULL,
	[VALIDITY] [datetime] NULL,
	[PRODUCER] [nvarchar](200) NULL,
	[SPEC] [nvarchar](100) NULL,
	[UNIT] [nvarchar](60) NULL,
	[QTY] [decimal](23, 10) NULL,
	[PROVIDER_NAME] [nvarchar](200) NULL,
	[PROVIDER_CODE] [nvarchar](40) NULL,
	[PROPERTY] [nvarchar](100) NULL,
	[ISDELETE] [bit] NULL
) ON [PRIMARY]

drop table [STORE]
CREATE TABLE [dbo].[STORE](
	[ID] [bigint] NOT NULL,
	[CreateTime] [datetime] NULL,
	[UpdateTime] [datetime] NULL,
	[PHAM_CODE] [nvarchar](40) NULL,
	[PHAM_NAME] [nvarchar](200) NULL,
	[PASS_NO] [nvarchar](80) NULL,
	[BATCH_NO] [nvarchar](50) NULL,
	[VALIDITY] [datetime] NULL,
	[PRODUCER] [nvarchar](200) NULL,
	[SPEC] [nvarchar](100) NULL,
	[UNIT] [nvarchar](60) NULL,
	[QTY] [decimal](23, 10) NULL,
	[PROPERTY] [nvarchar](100) NULL,
	[ISDELETE] [bit] NULL
) ON [PRIMARY]


CREATE VIEW [dbo].[InstrumentSaleView]
AS
SELECT   SALE_NO, SALE_DATE, INSTRUMENT_CODE, INSTRUMENT_NAME, REGIST_NO, BATCH_NO, 
case when VALIDITY = '1900-01-01 00:00:00.000' then '' else   CONVERT(varchar(100), VALIDITY, 121) end as VALIDITY, 
PRODUCER, 
                SPEC, UNIT, QTY, PURCHASER_NAME, PURCHASER_CODE, DESTROY_NO
FROM      InstrumentSale

CREATE VIEW [dbo].[InstrumentStockView]
AS
SELECT   PURCHASE_DATE, BUY_NO, INSTRUMENT_CODE, INSTRUMENT_NAME, REGIST_NO, BATCH_NO, 
case when VALIDITY = '1900-01-01 00:00:00.000' then '' else   CONVERT(varchar(100), VALIDITY, 121) end as VALIDITY,  
                PRODUCER, SPEC, UNIT, QTY, PROVIDER_NAME, PROVIDER_CODE, DESTROY_NO
FROM      InstrumentStock

CREATE VIEW [dbo].[InstrumentStoreView]
AS
SELECT   INSTRUMENT_CODE, INSTRUMENT_NAME, REGIST_NO, BATCH_NO, 
case when VALIDITY = '1900-01-01 00:00:00.000' then '' else   CONVERT(varchar(100), VALIDITY, 121) end as VALIDITY, 
 PRODUCER, SPEC, UNIT, QTY, 
                DESTROY_NO
FROM      InstrumentStore

CREATE VIEW [dbo].[SALEVIEW]
AS
SELECT   SALE_NO, SALE_DATE, PHAM_CODE, PHAM_NAME, PASS_NO, BATCH_NO, 
case when VALIDITY = '1900-01-01 00:00:00.000' then '' else   CONVERT(varchar(100), VALIDITY, 121) end as VALIDITY, 
 PRODUCER, SPEC, UNIT, QTY, 
                PURCHASER_NAME, PURCHASER_CODE, PROPERTY
FROM      SALE

CREATE VIEW [dbo].[STOCKVIEW]
AS
SELECT   PURCHASE_DATE, BUY_NO, PHAM_CODE, PHAM_NAME, PASS_NO, BATCH_NO, 
case when VALIDITY = '1900-01-01 00:00:00.000' then '' else   CONVERT(varchar(100), VALIDITY, 121) end as VALIDITY, 
 PRODUCER, SPEC, UNIT, 
                QTY, PROVIDER_NAME, PROVIDER_CODE, PROPERTY
FROM      STOCK


CREATE VIEW [dbo].[STOREVIEW]
AS
SELECT   PHAM_CODE, PHAM_NAME, PASS_NO, BATCH_NO, 
case when VALIDITY = '1900-01-01 00:00:00.000' then '' else   CONVERT(varchar(100), VALIDITY, 121) end as VALIDITY, 
 PRODUCER, SPEC, UNIT, QTY, PROPERTY
FROM      STORE


